package cn.jbolt.core.poi.excel;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.*;
import cn.hutool.poi.excel.cell.CellUtil;
import cn.jbolt.core.safe.XssUtil;
import cn.jbolt.core.util.JBoltArrayUtil;
import cn.jbolt.core.util.JBoltStringUtil;
import com.google.zxing.BarcodeFormat;
import com.google.zxing.client.j2se.MatrixToImageWriter;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.oned.Code128Writer;
import com.google.zxing.oned.Code39Writer;
import com.jfinal.core.converter.TypeConverter;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.Table;
import com.jfinal.plugin.activerecord.TableMapping;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Excel导入导出工具类
 *
 * @ClassName: JBoltExcelUtil
 * @author: JFinal学院-小木 QQ：909854136
 * @date: 2021年2月26日
 *
 */
public class JBoltExcelUtil {
	/**
	 * 处理单元格合并
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processExcelMerge(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		List<JBoltExcelMerge> merges = sheet.getMerges();
		if (CollUtil.isNotEmpty(merges)) {
			merges.forEach(merge -> {
				if (merge.getFirstRow() != merge.getLastRow() || merge.getFirstColumn() != merge.getLastColumn()) {
					excelWriter.merge(merge.getFirstRow() - 1, merge.getLastRow() - 1, merge.getFirstColumn() - 1,
							merge.getLastColumn() - 1, merge.getContent(), merge.isSetHeaderStyle());
				} else {
					CellUtil.setCellValue(excelWriter.getCell(merge.getLastColumn() - 1, merge.getLastRow() - 1, true),
							merge.getContent(), excelWriter.getStyleSet(), merge.isSetHeaderStyle());
				}
				if (merge.isPicData()) {
					writePic(excelWriter, merge.getFirstRow() - 1, merge.getFirstColumn() - 1, merge.getLastRow() - 1,
							merge.getLastColumn() - 1, (byte[]) merge.getPic(), Workbook.PICTURE_TYPE_JPEG);
				}
			});
		}
	}

	/**
	 * 处理导出数据的表头
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processWriteExcelHeaders(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		List<JBoltExcelHeader> headers = sheet.getHeaders();
		if (headers != null && headers.size() > 0 && sheet.isProcessHeaderAlias()) {
			// 设置别名
			for (JBoltExcelHeader header : headers) {
				excelWriter.addHeaderAlias(header.getColumn(), header.getContent());
			}
			// 设置只输出设置别名的数据列
			excelWriter.setOnlyAlias(true);
		}
		// 只有merge不设置覆盖header 并且header有值 我才输出header
		if (!sheet.isMergeAsHeader() && sheet.getExcel().isExport()) {
			if (sheet.getHeaderStartRow() > 0) {
				excelWriter.setCurrentRow(sheet.getHeaderStartRow() - 1);
			}
			excelWriter.writeHeadRow(sheet.getHeaders());
		}
	}
	/**
	 * 处理读取数据的表头
	 *
	 * @param excelReader
	 * @param jBoltExcel
	 */
//	private static void processReadExcelHeaders(ExcelReader excelReader, JBoltExcelSheet sheet) {
//		List<JBoltExcelHeader> headers = sheet.getHeaders();
//		if(headers!=null&&headers.size()>0&&sheet.isProcessHeaderAlias()) {
//			//设置别名
//			for (JBoltExcelHeader header : headers) {
//				excelReader.addHeaderAlias(header.getContent(),header.getColumn());
//			}
//		}
//	}

	/**
	 * 处理导出数据
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processExportExcelDatas(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		if (sheet.hasDatas()) {
			List<JBoltExcelData> datas = sheet.getDatas();
			JBoltExcelDataFilter filter = sheet.getDataFilter();
			//如果存在filter先过滤
			if(filter!=null){
				datas = datas.stream().filter(data->filter.accept(data)).collect(Collectors.toList());
			}
			JBoltExcelDataChangeHandler handler = sheet.getDataChangeHandler();
			int size = datas.size();
			if(size > 0){
				if (handler != null) {
					for (int i = 0; i < size; i++) {
						handler.change(datas.get(i), i);
					}
				}

				if (sheet.getDataStartRow() > 0) {
					excelWriter.setCurrentRow(sheet.getDataStartRow() - 1);
				}
				excelWriter.write(datas, false);

				JBoltExcelCellStyleHandler cellStyleHandler = sheet.getCellStyleHandler();
				int rowIndex = sheet.getDataStartRow() - 1;
				if (cellStyleHandler != null) {
					for (int i = 0; i < size; i++) {
						cellStyleHandler.process(excelWriter, sheet, rowIndex, datas.get(i), i);
						rowIndex++;
					}
				}
			}
		}
		// 如果有特殊定位输出datas
		if (sheet.hasPositionDatas()) {
			List<JBoltExcelPositionData> positionDatas = sheet.getPositionDatas();
			positionDatas.forEach(data -> {
				excelWriter.setCurrentRow(data.getRow() - 1);
				CellStyle cellStyle = data.getCellStyle();
				if (cellStyle == null) {
					cellStyle = excelWriter.getOrCreateCellStyle(data.getCol() - 1, data.getRow() - 1);
				}
				if (data.getValue() instanceof byte[]) {
					if (data.getLastRow() >= 1 && data.getLastCol() >= 1) {
						writePic(excelWriter, data.getRow() - 1, data.getCol() - 1, data.getLastRow() - 1,
								data.getLastCol() - 1, (byte[]) data.getValue(), Workbook.PICTURE_TYPE_JPEG);
					} else {
						writePic(excelWriter, data.getRow() - 1, data.getCol() - 1, (byte[]) data.getValue(),
								Workbook.PICTURE_TYPE_JPEG);
					}
				} else {
					excelWriter.writeCellValue(data.getCol() - 1, data.getRow() - 1, data.getValue());
				}
				excelWriter.getOrCreateCell(data.getCol() - 1, data.getRow() - 1).setCellStyle(cellStyle);
			});
		}

	}

	/**
	 * 写出图片
	 *
	 * @param writer
	 * @param rowStart
	 * @param colStart
	 * @param pictureData
	 * @param picType
	 */
	private static void writePic(ExcelWriter writer, int rowStart, int colStart, byte[] pictureData, int picType) {
		writePic(writer, rowStart, colStart, rowStart, colStart, pictureData, picType);
	}

	/**
	 * 写出图片
	 *
	 * @param writer
	 * @param rowStart
	 * @param colStart
	 * @param rowEnd
	 * @param colEnd
	 * @param pictureData
	 * @param picType
	 */
	private static void writePic(ExcelWriter writer, int rowStart, int colStart, int rowEnd, int colEnd,
								 byte[] pictureData, int picType) {
		Sheet sheet = writer.getSheet();
		Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();

		// 设置图片单元格位置
		ClientAnchor anchor = drawingPatriarch.createAnchor(0, 0, 0, 0, colStart, rowStart, colEnd + 1, rowEnd + 1);
		// 随单元格改变位置和大小
		anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

		// 添加图片
		int pictureIndex = sheet.getWorkbook().addPicture(pictureData, picType);
		drawingPatriarch.createPicture(anchor, pictureIndex);
	}

	/**
	 * 处理列宽
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	public static void processColumnWidth(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		if (sheet.hasHeaders()) {
			List<JBoltExcelHeader> headers = sheet.getHeaders();
			int size = headers.size(), width;
			for (int i = 0; i < size; i++) {
				width = headers.get(i).getWidth();
				if (width > 0) {
					excelWriter.setColumnWidth(i, width);
				} else {
					excelWriter.autoSizeColumn(i, true);
				}
			}

		}
	}

	/**
	 * 处理样式
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processExcelStyle(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		processColumnWidth(excelWriter, sheet);
		JBoltExcelStyleHandler styleHandler = sheet.getStyleHandler();
		if (styleHandler != null) {
			styleHandler.process(excelWriter);
		}

		if (sheet.hasCellStyles()) {
			sheet.getCellStyles().forEach(cellStyle -> {
				excelWriter.getCell(cellStyle.getCol(), cellStyle.getRow()).setCellStyle(cellStyle.getCellStyle());
			});
		}
	}
	/**
	 * 处理导出数据
	 *
	 * @param excelWriter
	 * @param jBoltExcel
	 *//*
		 * private void processExcelDatas(ExcelWriter excelWriter,JBoltExcel jBoltExcel)
		 * { List<Map<String, Object>> datas=jBoltExcel.getDatas(); if(isOk(datas)) {
		 * List<JBoltExcelHeader> headers=jBoltExcel.getHeaders(); int
		 * row=excelWriter.getCurrentRow(), maxRow=datas.size()+row,
		 * maxCol=jBoltExcel.getHeaders().size(), col=0; Map<String, Object> data;
		 * JBoltExcelHeader header; for(;row<maxRow;row++) { data=datas.get(row);
		 * for(;col<maxCol;col++) { header=headers.get(col);
		 * excelWriter.writeCellValue(row,col, data.get(header.getColumn())); } } } }
		 */

	/**
	 * 把JBoltExcel数据转为byte[]
	 *
	 * @param jBoltExcel
	 * @return
	 */
	public static byte[] getExcelBytes(JBoltExcel jBoltExcel) {
		ExcelWriter excelWriter = null;
		List<JBoltExcelSheet> sheets = jBoltExcel.getSheets();
		if (sheets != null && !sheets.isEmpty()) {
			if (jBoltExcel.isExportByTpl()) {
				if (jBoltExcel.isBig()) {
					Workbook workBook = WorkbookUtil.createSXSSFBook(FileUtil.getInputStream(jBoltExcel.getFromFile()));
					excelWriter = new BigExcelWriter(workBook.getSheetAt(0));
				} else {
					if (jBoltExcel.isXlsx()) {
						XSSFWorkbook xssfWorkbook = (XSSFWorkbook) WorkbookUtil
								.createBook(FileUtil.getInputStream(jBoltExcel.getFromFile()));
						excelWriter = new ExcelWriter(xssfWorkbook.getSheetAt(0));
					} else {
						Workbook workBook = WorkbookUtil.createBook(FileUtil.getInputStream(jBoltExcel.getFromFile()));
						excelWriter = new ExcelWriter(workBook.getSheetAt(0));
					}
				}
			} else {
				if (jBoltExcel.isBig()) {
					excelWriter = new BigExcelWriter(-1);
				} else {
					excelWriter = new ExcelWriter(jBoltExcel.isXlsx());
				}
			}

			List<String> sheetNames = excelWriter.getSheetNames();
			int index = 0;
			for (JBoltExcelSheet sheet : sheets) {
				// 处理哪个Sheet就设置为当前处理sheet切换好再进行下面的操作 保证一致

				if (StrKit.isBlank(sheet.getName()) && sheet.getSheetIndex() > 0) {
					if (sheetNames != null && sheetNames.size() >= sheet.getSheetIndex()) {
						sheet.setName(sheetNames.get(sheet.getSheetIndex() - 1));
					} else {
						sheet.setName("sheet" + sheet.getSheetIndex());
					}
				}
				if (sheet.getSheetIndex() > 0) {
					excelWriter.setSheet(sheet.getSheetIndex() - 1);
				} else if (StrKit.notBlank(sheet.getName())) {
					if (jBoltExcel.isExportByTpl()) {
						excelWriter.setSheet(sheet.getName());
					} else {
						excelWriter.setSheet(index);
					}
				}
				// 如果切换的和自己指定的名字不一样 就rename
				if (jBoltExcel.isExport() && StrKit.notBlank(sheet.getName())
						&& !excelWriter.getSheet().getSheetName().trim().equals(sheet.getName().trim())) {
					excelWriter.renameSheet(sheet.getName());
				}
				// 处理单元格合并
				processExcelMerge(excelWriter, sheet);
				// 处理表头生成 如果需要header直接使用merge合并数据作为标题的话 就不处理headers
				processWriteExcelHeaders(excelWriter, sheet);
				// 处理模板中需要移位的区域和数据
				processTplPositionChanges(excelWriter, sheet);
				// 处理导出数据
				processExportExcelDatas(excelWriter, sheet);
				// 处理公式
				processExportExcelFormula(excelWriter, sheet);
				// 样式处理
				processExcelStyle(excelWriter, sheet);
				// 处理水印
				processWaterMark(excelWriter, sheet.getWaterMark());
				index++;
			}

		} else {
			if (jBoltExcel.isBig()) {
				excelWriter = ExcelUtil.getBigWriter();
			} else {
				excelWriter = ExcelUtil.getWriter(jBoltExcel.isXlsx());
			}
		}

		// 转为byte[]
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		excelWriter.flush(os, true);
		excelWriter.close();
		return os.toByteArray();
	}

	/**
	 * 处理
	 *
	 * @param excelWriter
	 * @param waterMark
	 */
	private static void processWaterMark(ExcelWriter excelWriter, JBoltWaterMark waterMark) {
		if (excelWriter.isXlsx() && waterMark != null) {
			try {
				addXlsxWaterMark(excelWriter.getSheet(), waterMark);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 处理模板中需要移位的区域和数据 当模板中间填充数据 上下都有模板的时候 下方模板需要根据填充数据的量 动态移位
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processTplPositionChanges(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		// 只有从模板加载后导出 并且有数据和移位设置的时候才可以处理
		if (!sheet.getExcel().isExportByTpl() || !sheet.hasDatas() || !sheet.hasPositionChanges()) {
			return;
		}
		List<JBoltExcelPositionChange> positionChanges = sheet.getPositionChanges();
		positionChanges.forEach(positionChange -> {
			if (positionChange.isNeedChange()) {
				// 判断是否需要change
				processTplPositionChange(excelWriter, positionChange);
			}
		});
	}

	/**
	 * 执行处理导出模板中一个区域的位置变更
	 *
	 * @param excelWriter
	 * @param positionChange
	 */
	private static void processTplPositionChange(ExcelWriter excelWriter, JBoltExcelPositionChange positionChange) {
		// 原始区域坐标系
		int originFirstRow = positionChange.getOriginFirstRow() - 1;
		int originLastRow = positionChange.getOriginLastRow() - 1;
		int originFirstCol = positionChange.getOriginFirstCol() - 1;
		int originLastCol = positionChange.getOriginLastCol() - 1;
		int rowIncrease = positionChange.getRowIncrease();
		int colIncrease = positionChange.getColIncrease();

		Cell cell;
		Cell target;
		for (int row = originFirstRow; row <= originLastRow; row++) {
			for (int col = originFirstCol; col <= originLastCol; col++) {
				// 获取原始区域一个cell
				cell = excelWriter.getCell(col, row);
				if (cell != null) {
					// 获取变更区域一个cell
					target = excelWriter.getOrCreateCell(col + colIncrease, row + rowIncrease);
					if (target != null) {
						// 开始转移数据
						CellUtil.setCellValue(target, CellUtil.getCellValue(cell), cell.getCellStyle());
					}
				}
			}
		}
	}

	/**
	 * 处理公式
	 *
	 * @param excelWriter
	 * @param sheet
	 */
	private static void processExportExcelFormula(ExcelWriter excelWriter, JBoltExcelSheet sheet) {
		if (sheet.isForceFormulaRecalculation()) {
			excelWriter.getSheet().setForceFormulaRecalculation(true);
			// 如果有特殊定位输出datas
			if (sheet.hasCellFormluas()) {
				List<JBoltExcelPositionData> cellFormluas = sheet.getCellFormluas();
				cellFormluas.forEach(data -> {
					excelWriter.setCurrentRow(data.getRow() - 1);
					excelWriter.getOrCreateCell(data.getCol() - 1, data.getRow() - 1)
							.setCellFormula(data.getValue().toString());
				});
			}
		}
	}

	/**
	 * 从Excel中读取models 默认读取第一个sheet
	 *
	 * @param jBoltExcel
	 * @param modelClass
	 * @param errorMsg
	 * @return
	 */
	public static <T> List<T> readModels(JBoltExcel jBoltExcel, Class<T> modelClass, StringBuilder errorMsg) {
		if (jBoltExcel.getSheetSize() <= 0) {
			throw new RuntimeException("请设置要读取的sheets");
		}
		return readModels(jBoltExcel, jBoltExcel.getSheets().get(0), modelClass, errorMsg);
	}

	/**
	 * 从Excel中读取models 按照指定的sheetIndex
	 *
	 * @param jBoltExcel
	 * @param sheetIndex 第几个sheet 从1开始
	 * @param modelClass
	 * @param errorMsg
	 * @return
	 */
	public static <T> List<T> readModels(JBoltExcel jBoltExcel, int sheetIndex, Class<T> modelClass,
										 StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
			}
			return null;
		}
		return readModels(jBoltExcel, sheet, modelClass, errorMsg);
	}

	/**
	 * 从Excel中读取models 按照指定的sheet
	 *
	 * @param <T>
	 * @param jBoltExcel
	 * @param sheet
	 * @param modelClass
	 * @param errorMsg
	 * @return
	 */
	private static <T> List<T> readModels(JBoltExcel jBoltExcel, JBoltExcelSheet sheet, Class<T> modelClass,
										  StringBuilder errorMsg) {
		if (StrKit.isBlank(sheet.getName()) && sheet.getSheetIndex() < 1) {
			throw new RuntimeException("JBoltExcelSheet 必须设置读取的name或者sheetIndex");
		}
		if (sheet.getSheetIndex() > jBoltExcel.getSheetSize()) {
			throw new RuntimeException("JBoltExcelSheet 设置的sheetIndex[" + sheet.getSheetIndex() + "] 不能大于所读文件的总数【"
					+ jBoltExcel.getSheetSize() + "】");
		}
		List<JBoltExcelData> datas = readJBoltExcelDatas(jBoltExcel, sheet, true, errorMsg);
		if (datas == null || datas.size() == 0) {
			return null;
		}

		List<T> ts = new ArrayList<T>();
		T t;
		JBoltExcelData excelData;
		int size = datas.size();
		for (int i = 0; i < size; i++) {
			excelData = datas.get(i);
			t = toModel(modelClass, excelData, false, errorMsg);
			if (t != null) {
				ts.add(t);
			}
		}

		return ts;
	}

	/**
	 * 从Excel中读取models
	 *
	 * @param jBoltExcel
	 * @param sheetName
	 * @param modelClass
	 * @param errorMsg
	 * @return
	 */
	public static <T> List<T> readModels(JBoltExcel jBoltExcel, String sheetName, Class<T> modelClass,
										 StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
			}
			return null;
		}
		return readModels(jBoltExcel, sheet, modelClass, errorMsg);
	}

	/**
	 * 读取Excel数据 按照指定的sheetIndex
	 *
	 * @param jBoltExcel
	 * @param sheetIndex
	 * @param ignoreEmptyRow
	 * @param errorMsg
	 * @return
	 */
	private static List<Map<String, Object>> readDatas(JBoltExcel jBoltExcel, int sheetIndex, boolean ignoreEmptyRow,
													   StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
			}
			return null;
		}
		return readDatas(jBoltExcel, sheet, ignoreEmptyRow);
	}

	/**
	 * 读取Excel数据 按照指定的name
	 *
	 * @param jBoltExcel
	 * @param sheetName
	 * @param ignoreEmptyRow
	 * @param errorMsg
	 * @return
	 */
	private static List<Map<String, Object>> readDatas(JBoltExcel jBoltExcel, String sheetName, boolean ignoreEmptyRow,
													   StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
			}
			return null;
		}
		return readDatas(jBoltExcel, sheet, ignoreEmptyRow);
	}

	/**
	 * 读取excel数据 按照指定sheetIndex 返回List<List<Object>>
	 *
	 * @param jBoltExcel
	 * @param sheetIndex
	 * @param errorMsg
	 * @return
	 */
	private static List<List<Object>> readMapperDatas(JBoltExcel jBoltExcel, int sheetIndex, StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
			}
			return null;
		}
		return readMapperDatas(jBoltExcel, sheet);
	}

	/**
	 * 读取excel数据 按照指定的name 返回List<List<Object>>
	 *
	 * @param jBoltExcel
	 * @param sheetName
	 * @param errorMsg
	 * @return
	 */
	private static List<List<Object>> readMapperDatas(JBoltExcel jBoltExcel, String sheetName, StringBuilder errorMsg) {
		JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
		if (sheet == null) {
			if (errorMsg != null) {
				errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
			}
			return null;
		}
		return readMapperDatas(jBoltExcel, sheet);
	}

	/**
	 * 读取excel数据 返回List<List<Object>>
	 *
	 * @param jBoltExcel
	 * @param sheet
	 * @return
	 */
	private static List<List<Object>> readMapperDatas(JBoltExcel jBoltExcel, JBoltExcelSheet sheet) {
		if (StrKit.isBlank(sheet.getName()) && sheet.getSheetIndex() < 1) {
			throw new RuntimeException("JBoltExcelSheet 必须设置读取的name或者sheetIndex");
		}
		if (sheet.getSheetIndex() > jBoltExcel.getSheetSize()) {
			throw new RuntimeException("JBoltExcelSheet 设置的sheetIndex[" + sheet.getSheetIndex() + "] 不能大于所读文件的总数【"
					+ jBoltExcel.getSheetSize() + "】");
		}
		ExcelReader excelReader = null;
		try{
			if (sheet.getSheetIndex() > 0) {
				excelReader = ExcelUtil.getReader(jBoltExcel.getFromFile(), sheet.getSheetIndex() - 1);
			} else if (StrKit.notBlank(sheet.getName())) {
				excelReader = ExcelUtil.getReader(jBoltExcel.getFromFile(), sheet.getName());
			}
		}catch (Exception e){
			if(e.getMessage().contains("No Sheet provided")){
				throw new RuntimeException("上传Excel文件中找不到名为["+sheet.getName()+"]的sheet工作表");
			}
		}
		excelReader.setIgnoreEmptyRow(false);
		int endRowIndex = excelReader.getRowCount() - 1;
		List<List<Object>> datas = null;
		try {
			datas = excelReader.read(0, endRowIndex, false);
		} finally {
			excelReader.close();
		}
		// 存在数据 就要处理xss
		if (datas != null && datas.size() > 0) {
			datas.forEach(data -> data.forEach(value -> XssUtil.processExcelString(value)));
		}
		return datas;
	}

		/**
		 * 读取excel数据
		 *
		 * @param jBoltExcel
		 * @param sheet
		 * @return
		 */
		private static List<Map<String, Object>> readDatas(JBoltExcel jBoltExcel, JBoltExcelSheet sheet,
		boolean ignoreEmptyRow) {
			List<JBoltExcelHeader> headerColumns = sheet.getHeaders();
			if (headerColumns == null || headerColumns.size() == 0) {
				return null;
			}
			if (StrKit.isBlank(sheet.getName()) && sheet.getSheetIndex() < 1) {
				throw new RuntimeException("JBoltExcelSheet 必须设置读取的name或者sheetIndex");
			}
			if (sheet.getSheetIndex() > jBoltExcel.getSheetSize()) {
				throw new RuntimeException("JBoltExcelSheet 设置的sheetIndex[" + sheet.getSheetIndex() + "] 不能大于所读文件的总数【"
						+ jBoltExcel.getSheetSize() + "】");
			}
			ExcelReader excelReader = null;
			try {
				if (sheet.getSheetIndex() > 0) {
					excelReader = ExcelUtil.getReader(jBoltExcel.getFromFile(), sheet.getSheetIndex() - 1);
				} else if (StrKit.notBlank(sheet.getName())) {
					excelReader = ExcelUtil.getReader(jBoltExcel.getFromFile(), sheet.getName());
				}
			}catch (Exception e){
				if(e.getMessage().contains("No Sheet provided")){
					throw new RuntimeException("上传Excel文件中找不到名为["+sheet.getName()+"]的sheet工作表");
				}
			}
			excelReader.setIgnoreEmptyRow(ignoreEmptyRow);
			int dataStartRow = sheet.getDataStartRow() > 0 ? (sheet.getDataStartRow() - 1) : 0;
			int endRowIndex = excelReader.getRowCount() - 1;
			List<List<Object>> rowDatas = null;
			try{
				rowDatas = excelReader.read(dataStartRow, endRowIndex, false);
				if (rowDatas == null || rowDatas.size() == 0) {
					return null;
				}
			}finally {
				excelReader.close();
			}
			List<Map<String, Object>> mapDatas = new ArrayList<Map<String, Object>>();
			Map<String, Object> temp;
			int i = 0;
			int rowColSize;
			Object value;
			for (List<Object> rowData : rowDatas) {
				i = 0;
				rowColSize = rowData.size();
				temp = new LinkedHashMap<String, Object>();
				for (JBoltExcelHeader column : headerColumns) {
					if (column.getCol() > 0) {
						if (column.getCol() <= rowColSize) {
							value = rowData.get(column.getCol() - 1);
							temp.put(column.getColumn(), XssUtil.processExcelString(value));
						} else {
							temp.put(column.getColumn(), null);
						}
					} else {
						if (i < rowColSize) {
							value = rowData.get(i);
							temp.put(column.getColumn(), XssUtil.processExcelString(value));
						} else {
							temp.put(column.getColumn(), null);
						}
					}
					i++;
				}
				mapDatas.add(temp);

			}
			return mapDatas;
		}

		/**
		 * 从Excel中读取records 按照指定的sheetIndex
		 *
		 * @param jBoltExcel
		 * @param sheetIndex
		 * @param ignoreEmptyRow
		 * @param errorMsg
		 * @return
		 */
		public static List<Record> readRecords(JBoltExcel jBoltExcel, int sheetIndex, boolean ignoreEmptyRow,
		StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
			if (sheet == null) {
				if (errorMsg != null) {
					errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
				}
				return null;
			}
			return readRecords(jBoltExcel, sheet, ignoreEmptyRow, errorMsg);
		}

		/**
		 * 从Excel中读取records 按照指定的name
		 *
		 * @param jBoltExcel
		 * @param sheetName
		 * @param ignoreEmptyRow
		 * @param errorMsg
		 * @return
		 */
		public static List<Record> readRecords(JBoltExcel jBoltExcel, String sheetName, boolean ignoreEmptyRow,
		StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
			if (sheet == null) {
				if (errorMsg != null) {
					errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
				}
				return null;
			}
			return readRecords(jBoltExcel, sheet, ignoreEmptyRow, errorMsg);
		}

		/**
		 * 从Excel中读取records
		 *
		 * @param jBoltExcel
		 * @param sheet
		 * @param ignoreEmptyRow
		 * @param errorMsg
		 * @return
		 */
		public static List<Record> readRecords(JBoltExcel jBoltExcel, JBoltExcelSheet sheet, boolean ignoreEmptyRow,
		StringBuilder errorMsg) {
			List<Map<String, Object>> datas = readDatas(jBoltExcel, sheet, ignoreEmptyRow);
			if (datas == null || datas.size() == 0) {
				return null;
			}
			List<Record> records = new ArrayList<Record>();
			JBoltExcelDataChangeHandler changeHandler = sheet.getDataChangeHandler();
			boolean doChangeHandler = false;
			if (changeHandler != null) {
				doChangeHandler = true;
			}
			Record record;
			int size = datas.size();
			Map<String, Object> data;
			JBoltExcelData jed;
			for (int i = 0; i < size; i++) {
				data = datas.get(i);
				if (doChangeHandler) {
					jed = JBoltExcelData.fromMap(data);
					changeHandler.change(jed, i);
					record = toRecord(jed);
				}else{
					record = toRecord(data);
				}
				if (record != null) {
					records.add(record);
				}
			}
			return records;
		}

		/**
		 * 从Excel中读取List<JBoltExcelData> 按照指定的sheetIndex
		 *
		 * @param jBoltExcel
		 * @param sheetIndex
		 * @param ignoreEmptyRow
		 * @param errorMsg
		 * @return
		 */
		public static List<JBoltExcelData> readJBoltExcelDatas(JBoltExcel jBoltExcel, int sheetIndex,
		boolean ignoreEmptyRow, StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
			if (sheet == null) {
				errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
				return null;
			}
			return readJBoltExcelDatas(jBoltExcel, sheet, ignoreEmptyRow, errorMsg);
		}

		/**
		 * 从Excel中读取List<JBoltExcelData> 按照指定的name
		 *
		 * @param jBoltExcel
		 * @param sheetName
		 * @param ignoreEmptyRow
		 * @param doChangeHandler
		 * @param errorMsg
		 * @return
		 */
		public static List<JBoltExcelData> readJBoltExcelDatas(JBoltExcel jBoltExcel, String sheetName,
		boolean ignoreEmptyRow, boolean doChangeHandler, StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
			if (sheet == null) {
				errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
				return null;
			}
			return readJBoltExcelDatas(jBoltExcel, sheet, ignoreEmptyRow, errorMsg);
		}

		/**
		 * 从Excel中读取List<JBoltExcelData>
		 *
		 * @param jBoltExcel
		 * @param sheet
		 * @param ignoreEmptyRow
		 * @param errorMsg
		 * @return
		 */
		public static List<JBoltExcelData> readJBoltExcelDatas(JBoltExcel jBoltExcel, JBoltExcelSheet sheet,
		boolean ignoreEmptyRow, StringBuilder errorMsg) {
			List<Map<String, Object>> datas = readDatas(jBoltExcel, sheet, ignoreEmptyRow);
			if (datas == null || datas.size() == 0) {
				return null;
			}
			List<JBoltExcelData> excelDatas = new ArrayList<JBoltExcelData>();
			JBoltExcelDataChangeHandler changeHandler = sheet.getDataChangeHandler();
			JBoltExcelDataFilter filter = sheet.getDataFilter();
			boolean doChangeHandler = false;
			if (changeHandler != null) {
				doChangeHandler = true;
			}
			int size = datas.size();
			JBoltExcelData data;
			if(filter == null){
				for (int i = 0; i < size; i++) {
					data = JBoltExcelData.fromMap(datas.get(i));
					if (doChangeHandler) {
						changeHandler.change(data, i);
					}
					excelDatas.add(data);
				}
			}else{
				for (int i = 0; i < size; i++) {
					data = JBoltExcelData.fromMap(datas.get(i));
					if(filter.accept(data)){
						if (doChangeHandler) {
							changeHandler.change(data, i);
						}
						excelDatas.add(data);
					}
				}
			}
			return excelDatas;
		}

		/**
		 * 从Excel中读取JBoltExcelDataMapper
		 *
		 * @param jBoltExcel
		 * @param sheet
		 * @return
		 */
		public static JBoltExcelDataMapper readDataMapper(JBoltExcel jBoltExcel, JBoltExcelSheet sheet) {
			List<List<Object>> datas = readMapperDatas(jBoltExcel, sheet);
			return JBoltExcelDataMapper.create(datas);
		}

		/**
		 * 从Excel中读取JBoltExcelDataMapper 按照指定的sheetIndex
		 *
		 * @param jBoltExcel
		 * @param sheetIndex
		 * @param errorMsg
		 * @return
		 */
		public static JBoltExcelDataMapper readDataMapper(JBoltExcel jBoltExcel, int sheetIndex, StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetIndex);
			if (sheet == null) {
				if (errorMsg != null) {
					errorMsg.append("Excel中第[").append(sheetIndex).append("]个工作表不存在! ");
				}
				return null;
			}
			return readDataMapper(jBoltExcel, sheet);
		}

		/**
		 * 从Excel中读取JBoltExcelDataMapper
		 *
		 * @param jBoltExcel
		 * @param sheetName
		 * @param errorMsg
		 * @return
		 */
		public static JBoltExcelDataMapper readDataMapper(JBoltExcel jBoltExcel, String sheetName, StringBuilder errorMsg) {
			JBoltExcelSheet sheet = jBoltExcel.getSheet(sheetName);
			if (sheet == null) {
				if (errorMsg != null) {
					errorMsg.append("Excel中名为[").append(sheetName).append("]的工作表不存在! ");
				}
				return null;
			}
			return readDataMapper(jBoltExcel, sheet);
		}

		public static final Record toRecord(JBoltExcelData data) {
			return new Record().setColumns(data);
		}

		public static final Record toRecord(Map<String, Object> data) {
			return new Record().setColumns(data);
		}

		@SuppressWarnings("unchecked")
		public static final <T> T toModel(Class<T> modelClass, JBoltExcelData data, boolean skipConvertError,
		StringBuilder errorMsg) {
			Object temp = null;
			try {
				temp = modelClass.newInstance();
			} catch (Exception e) {
				errorMsg.append("指定Modelclass无效，无法实例化");
				return null;
			}
			Model<?> model = (Model<?>) temp;
			Table table = TableMapping.me().getTable(model.getClass());
			if (table == null) {
				errorMsg.append("The Table mapping of model: ").append(modelClass.getName())
						.append(" not exists or the ActiveRecordPlugin not start.");
				return null;
			}

			TypeConverter converter = TypeConverter.me();
			// 对 paraMap进行遍历而不是对table.getColumnTypeMapEntrySet()进行遍历，以便支持
			// CaseInsensitiveContainerFactory
			// 以及支持界面的 attrName有误时可以感知并抛出异常避免出错
			// 得到数据的columns
			List<String> columns = CollUtil.newArrayList(data.keySet());
			int size = columns.size();
			String attrName;
			Object colData;
			for (int i = 0; i < size; i++) {
				attrName = columns.get(i);
				Class<?> colType = null;
				if (table.hasColumnLabel(attrName)) {
					colType = table.getColumnType(attrName);
				} else {
					if (attrName.indexOf("_") == -1) {
						attrName = StrUtil.toUnderlineCase(attrName);
						if (table.hasColumnLabel(attrName)) {
							colType = table.getColumnType(attrName);
						}
					}
				}
				if (colType == null) {
					if (skipConvertError) {
						continue;
					} else {
						errorMsg.append("The model attribute ").append(attrName).append(" is not exists.");
						return null;
					}
				}

				try {
					colData = data.get(attrName);
					Object value = colData != null ? converter.convert(colType, colData.toString()) : null;
					model.set(attrName, value);
				} catch (Exception e) {
					if (skipConvertError == false) {
						errorMsg.append("Can not convert parameter: ").append(attrName);
						return null;
					}
				}
			}

			return (T) model;
		}

		/**
		 * 英文字符串转数字 从1开始
		 *
		 * @param colStr
		 * @return
		 */
		public static int colStrToNum(String colStr) {
			return colStrToNum(colStr, colStr.length());
		}

		/**
		 * 英文字符串转数字 从1开始
		 *
		 * @param colStr
		 * @param length
		 * @return
		 */
		public static int colStrToNum(String colStr, int length) {
			int num = 0;
			int result = 0;
			for (int i = 0; i < length; i++) {
				char ch = colStr.charAt(length - i - 1);
				num = (int) (ch - 'A' + 1);
				num *= Math.pow(26, i);
				result += num;
			}
			return result;
		}

		/**
		 * 从1开始的数字列转英文字符串
		 *
		 * @param columnNum
		 * @return
		 */
		public static String colNumToStr(int columnNum) {
			if (columnNum <= 0) {
				return null;
			}
			String columnStr = "";
			columnNum--;
			do {
				if (columnStr.length() > 0) {
					columnNum--;
				}
				columnStr = ((char) (columnNum % 26 + (int) 'A')) + columnStr;
				columnNum = (int) ((columnNum - columnNum % 26) / 26);
			} while (columnNum > 0);
			return columnStr;
		}

		/**
		 * excel position转row col数组
		 *
		 * @param position
		 * @return
		 */
		public static int[] positionStrSplit(String position) {
			if (StrKit.isBlank(position)) {
				return new int[] { 0, 0 };
			}
			if (position.indexOf(":") != -1) {
				// 不带冒号的字符串类似这种 E5 第五行 第五列
				String[] arr = JBoltArrayUtil.from(position, ":");
				if (arr == null || arr.length != 2) {
					return new int[] { 0, 0 };
				}
				return new int[] { Integer.parseInt(arr[1]), colStrToNum(arr[0]) };
			}
			String colStr = JBoltStringUtil.removeNumber(position);
			String rowStr = JBoltStringUtil.getNumber(position);
			if (StrKit.isBlank(colStr) || StrKit.isBlank(rowStr)) {
				return new int[] { 0, 0 };
			}
			return new int[] { Integer.parseInt(rowStr), colStrToNum(colStr) };
		}

		/**
		 * 保存到文件
		 *
		 * @param jBoltExcel
		 * @param saveFilePath
		 * @return
		 */
		public static File saveFile(JBoltExcel jBoltExcel, String saveFilePath) {
			return FileUtil.writeBytes(jBoltExcel.toByteArray(), saveFilePath);
		}

		/**
		 * 使用zxing创建barcode128
		 *
		 * @param content
		 * @param width
		 * @param height
		 * @param pictureType
		 * @return
		 */
		public static byte[] createBarcode128PicByZxing(String content, int width, int height, int pictureType) {
			String format = "png";
			if (pictureType == Workbook.PICTURE_TYPE_JPEG) {
				format = "jpg";
			}
			byte[] datas = null;
			Code128Writer writer = new Code128Writer();
			BitMatrix bitMatrix = writer.encode(content, BarcodeFormat.CODE_128, width, height);
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			try {
				MatrixToImageWriter.writeToStream(bitMatrix, format, os);
				datas = os.toByteArray();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (os != null) {
					try {
						os.close();
					} catch (IOException e1) {
						e1.printStackTrace();
					}
				}
			}
			return datas;
		}

		/**
		 * 使用zxing创建barcode39
		 *
		 * @param content
		 * @param width
		 * @param height
		 * @param pictureType
		 * @return
		 */
		public static byte[] createBarcode39PicByZxing(String content, int width, int height, int pictureType) {
			String format = "png";
			if (pictureType == Workbook.PICTURE_TYPE_JPEG) {
				format = "jpg";
			}
			byte[] datas = null;
			Code39Writer writer = new Code39Writer();
			BitMatrix bitMatrix = writer.encode(content, BarcodeFormat.CODE_39, width, height);
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			try {
				MatrixToImageWriter.writeToStream(bitMatrix, format, os);
				datas = os.toByteArray();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (os != null) {
					try {
						os.close();
					} catch (IOException e1) {
						e1.printStackTrace();
					}
				}
			}
			return datas;
		}

		/**
		 * 给excel加水印
		 *
		 * @param sheet
		 * @param waterMark
		 * @throws IOException
		 */
		public static void addXlsxWaterMark(Sheet sheet, JBoltWaterMark waterMark) throws IOException {
			BufferedImage image = JBoltWaterMarkUtil.createJBoltWatermarkImage(waterMark);
			// 导出到字节流B
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			ImageIO.write(image, "png", os);
			XSSFSheet xssfSheet = (XSSFSheet) sheet;
			XSSFWorkbook workbook = xssfSheet.getWorkbook();
			int pictureIdx = workbook.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
			POIXMLDocumentPart pictureData = workbook.getAllPictures().get(pictureIdx);
			PackagePartName ppn = pictureData.getPackagePart().getPartName();
			String relType = XSSFRelation.IMAGES.getRelation();
			// add relation from sheet to the picture data
			PackageRelationship pr = xssfSheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
			// set background picture to sheet
			xssfSheet.getCTWorksheet().addNewPicture().setId(pr.getId());

		}
}
